Files to be imported must adhere to specific details in order to import correctly.
Idealpos will accept files in .txt and .csv format.
Below is a list of the headers that are required when importing.
If the headers are not correct, the file may still import, but not all the data will be entered.
Make sure that you do not use specific characters that may harm the data integrity.
Characters that can sometimes cause issues are - , ‘“;: ` ~ ( ).
Headers must be in UPPERCASE to be imported correctly.
If you don’t intend to import a specific field, unless it is a required field, you can leave the header and field off the import file completely.
Note!
When importing data into fields defined by the Header, note the following:
If the last field of the last record in your import file does not contain any data, the Import will fail with "62-Input past end of file".
To prevent this from occurring, ensure that the field contains data.
Should you want to leave the last field of the last record blank on purpose, ensure that you press "ENTER" at the end of the line to ensure that the file imports successfully.
E.g. The third item in the below example does not contain a value for the Weight.
To ensure that it imports correctly without errors, press ENTER at the end of the last record
CODE,DESC,DPT,WEIGHT
1,ITEMONE,402,5
2,ITEMTWO,402,2
3,ITEMTHREE,402,
The following types of data can be imported into Idealpos:
CODE, DESC, SC, PRICE, TAX1, TAX2, TAX3, TAX4, TAX5, TAX6, NONTURN, RATE, PER, SCALE
Minimum Requirement for Import: CODE, DESC, SC
Field: | Length: |
CODE | 4 |
DESC | 30 |
SC | 2 |
PRICE | 10 (incl. decimal place) |
TAX1 | 1 (value can be 1 or 0) |
TAX2 | 1 (value can be 1 or 0) |
TAX3 | 1 (value can be 1 or 0) |
TAX4 | 1 (value can be 1 or 0) |
TAX5 | 1 (value can be 1 or 0) |
TAX6 | 1 (value can be 1 or 0) |
NONTURN | 1 (value can be 1 or 0) |
RATE | 7 |
PER | 10 |
SCALE | 1 (value can be 1 or 0) |
Stock Item Codes and Scan Codes must be unique.
If the items being imported are a new item, it must contain CODE, DESC and DPT.
If the cell is a field that does not contain Numbers or Words, but needs to be ticked to be selected (STOCKCONTROL), you can use (TRUE or 1) to tick the field, or (FALSE or 0) to leave it blank.
Stock Item descriptions (Description 1 and Description 2) must be 30 characters or less, or they will be truncated.
Stock Item Description 3 must be 100 characters or less.
Important Note!
When Importing Stock Items via an Import File, you MUST have a TAX2 field specified.
Failing to include a TAX2 field in your Import File will result in all Stock Items importing with GST enabled.
Minimum Requirement for Import: CODE, DESC, DPT
CODE, DESC, DPT, SCAN, STDCST, LSTCST, AVGCST, STOCKCONTROL, INDIRECT, PRICE1, PRICE2, PRICE3, PRICE4, PRICE5, PRICE6, PRICE7, PRICE8, PRICE9, PRICE10, PRICE11, PRICE12, UPLIM, LOWLIM, TAX1, TAX2, TAX3, TAX4, TAX5, TAX6, PC, POINTS, USEPOINTS, SUPP, SUPPCODE, DESC2, DESC3, NOTES, RECEIPT, PRINTER1, PRINTER2, PRINTER3, PRINTER4, PRINTER5, PRINTER6, PRINTER7, PRINTER8, PRINTER9, PRINTER10, PRINTER11, PRINTER12, NON-ACCUMULATING, SCALE, INHIBITDISCOUNTS, INSTRUCTION, PRICE1POINTS, PRICE2POINTS, PRICE3POINTS, PRICE4POINTS, PRICE5POINTS, PRICE6POINTS, PRICE7POINTS, PRICE8POINTS, PRICE9POINTS, PRICE10POINTS, PRICE11POINTS, PRICE12POINTS, WEBSTORE, INHIBITVOIDS, PRINTRED, FORCESELLINGPRICEENTRY, SALESPROMPT, UNIT, INHIBITPOINTS, INHIBITPURCHASEWITHPOINTS, DISCONTINUED, CHANGED, GRIDLINK, TARE, RECEIPTTABS, LINKTABLE, COMPONENTSTOKITCHEN, MENUCODE, PROMOTIONTABLE, PRINTPROMOTIONALTICKETS, PROMOTIONALGROUP, NATIONALPRODUCTNUMBER, INHIBITPURCHASEWITHGIFTVOUCHER, EXTRAFLAG2, SITE, DATECREATED, DATEMODIFIED, SUPPLIERQUANTITYTYPE, SUPPLIERSELLQUANTITY, WEIGHT, MODIFIERGRIDS
Issues can occur if you import the SUPP and SUPPCODE fields in separate files.
It is advisable to ensure both of these fields are imported within the same file if they are being used.
When using the SUPPLIERQUANTITYTYPE and SUPPLIERSELLQUANTITY options in the import file, the Supplier Quantity Type of Cartons or Units must be specified.
The Supplier Sell Quantity needs to be defined as a number value.
The Supplier and Supplier Code also need to be included and the Stock Item must not already exist in the Supplier Stock Codes.
When all of these conditions have been met, the Supplier Stock Code record will be created with the specified Quantity Type and Sell Quantity from the Import file.
If the Supplier Stock Code already exists in the system, the SUPPLIERQUANTITYTYPE and SUPPLIERSELLQUANTITY values will not be modified for the existing Supplier Stock Code.
MODIFIERGRIDS must be entered as a pipe separated list (the pipe character "|" is typically entered by holding down SHIFT and pressing the backslash "\" character on the keyboard). E.g. To specify Modifier Grids 1 and 10 in that order, this should be entered as "1|10".
Field: | Length: |
CODE | 16 |
DESC | 30 |
DPT | 4 |
SCAN | 13 |
STDCST | 10 (incl. decimal place) |
LSTCST | 10 (incl. decimal place) |
AVGCST | 10 (incl. decimal place) |
STOCKCONTROL | 1 (value can be 1 or 0) |
INDIRECT | 1 (value can be 1 or 0) |
PRICE1 | 10 (incl. decimal place) |
PRICE2 | 10 (incl. decimal place) |
PRICE3 | 10 (incl. decimal place) |
PRICE4 | 10 (incl. decimal place) |
PRICE5 | 10 (incl. decimal place) |
PRICE6 | 10 (incl. decimal place) |
PRICE7 | 10 (incl. decimal place) |
PRICE8 | 10 (incl. decimal place) |
PRICE9 | 10 (incl. decimal place) |
PRICE10 | 10 (incl. decimal place) |
PRICE11 | 10 (incl. decimal place) |
PRICE12 | 10 (incl. decimal place) |
UPLIM | 10 (incl. decimal place) |
LOWLIM | 10 (incl. decimal place) |
TAX1 | 1 (value can be 1 or 0) |
TAX2 | 1 (value can be 1 or 0) |
TAX3 | 1 (value can be 1 or 0) |
TAX4 | 1 (value can be 1 or 0) |
TAX5 | 1 (value can be 1 or 0) |
TAX6 | 1 (value can be 1 or 0) |
PC | 2 |
POINTS | 5 |
USEPOINTS | 1 (value can be 1 or 0) |
SUPP | 10 |
SUPPCODE | 16 |
DESC2 | 30 |
DESC3 | 100 |
NOTES | No limit, note that "_" character can be used to go to next line |
RECEIPT | |
PRINTER1 | 1 (value can be 1 or 0) |
PRINTER2 | 1 (value can be 1 or 0) |
PRINTER3 | 1 (value can be 1 or 0) |
PRINTER4 | 1 (value can be 1 or 0) |
PRINTER5 | 1 (value can be 1 or 0) |
PRINTER6 | 1 (value can be 1 or 0) |
PRINTER7 | 1 (value can be 1 or 0) |
PRINTER8 | 1 (value can be 1 or 0) |
PRINTER9 | 1 (value can be 1 or 0) |
PRINTER10 | 1 (value can be 1 or 0) |
PRINTER11 | 1 (value can be 1 or 0) |
PRINTER12 | 1 (value can be 1 or 0) |
NON-ACCUMULATING | 1 (value can be 1 or 0) |
SCALE | 1 (value can be 1 or 0) |
INHIBITDISCOUNTS | 1 (value can be 1 or 0) |
INSTRUCTION | 1 (value can be 1 or 0) |
PRICE1POINTS | 10 (incl. decimal place) |
PRICE2POINTS | 10 (incl. decimal place) |
PRICE3POINTS | 10 (incl. decimal place) |
PRICE4POINTS | 10 (incl. decimal place) |
PRICE5POINTS | 10 (incl. decimal place) |
PRICE6POINTS | 10 (incl. decimal place) |
PRICE7POINTS | 10 (incl. decimal place) |
PRICE8POINTS | 10 (incl. decimal place) |
PRICE9POINTS | 10 (incl. decimal place) |
PRICE10POINTS | 10 (incl. decimal place) |
PRICE11POINTS | 10 (incl. decimal place) |
PRICE12POINTS | 10 (incl. decimal place) |
WEBSTORE | 1 (value can be 1 or 0) |
INHIBITVOIDS | 1 (value can be 1 or 0) |
PRINTRED | 1 (value can be 1 or 0) |
FORCESELLINGPRICEENTRY | 1 (value can be 1 or 0) |
SALESPROMPT | 30 |
UNIT | 3 |
INHIBITPOINTS | 1 (value can be 1 or 0) |
INHIBITPURCHASEWITHPOINTS | 1 (value can be 1 or 0) |
DISCONTINUED | 1 (value can be 1 or 0) |
CHANGED | 1 (value can be 1 or 0) |
GRIDLINK | 4 |
TARE | 6 (incl. decimal place) |
RECEIPTTABS | 1 |
LINKTABLE | 3 |
COMPONENTSTOKITCHEN | 1 (value can be 1 or 0) |
MENUCODE | |
PROMOTIONTABLE | |
PRINTPROMOTIONALTICKETS | 1 (value can be 1 or 0) |
PROMOTIONALGROUP | |
NATIONALPRODUCTNUMBER | 10 |
INHIBITPURCHASEWITHGIFTVOUCHER | 1 (value can be 1 or 0) |
EXTRAFLAG2 | |
SITE | |
DATECREATED | |
DATEMODIFIED | |
SUPPLIERQUANTITYTYPE | |
SUPPLIERSELLQUANTITY | |
WEIGHT | 9 (decimal places are counted towards the length) |
MODIFIERGRIDS | Modifier Grids must be entered as a pipe separated list (pipe character is "|") which can be entered by typically holding down SHIFT and pressing the backslash character on the keyboard "\". Example - if 1 and 10 need to be entered as Modifier Grids, they should be entered as "1|10". |
Stock Items can be imported and at the same time, assigned to existing Attributes via an Import File.
You can do this by either specifying the Attribute Codes, or by specifying the Attribute Description.
Important Note!
This function is simply used for importing Stock Items and assigning the items to pre-existing attributes that have already been created in the database; this function will not import new Attributes.
You need to ensure that the Attributes already exist in your system by manually creating them
Please ensure that the required Attributes exist in the Idealpos database.
Once you have manually created the required Attributes, you can then import Stock Items and assign those Stock Items to existing Attributes via an Import File as outlined below.
There are two methods to assign Attributes when importing Stock Items:
Refer to the relevant heading below for the method you want to use to assign Attributes to the Stock Items that are being imported.
To specify the Attribute Codes with your Stock Item data, you must include the following headers in your Stock Item Import data for each Attribute that you are going to set:
ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5
An example of using Attribute Codes when importing a Stock Item is as follows:
CODE,DESCRIPTION,DEPARTMENT,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5
1,Test Stock Item,402,4,18,29,41,34
The Attribute Codes included in the above Import file example are shown below in the Stock Item Attributes window (accessed via Back Office > File > Sales > Stock Item Attributes):
After the item has been imported using the sample import file shown above, the Attributes will be assigned to the item as shown in the example below (bottom-right corner in the Attributes section).
To specify the Attribute Descriptions with your Stock Item data, you must include the following headers in your Stock Item Import data for each Attribute that you are going to set:
ATTRIBUTE1DESC,ATTRIBUTE2DESC,ATTRIBUTE3DESC,ATTRIBUTE4DESC,ATTRIBUTE5DESC
An example of using Attribute Descriptions when importing a Stock Item is as follows:
CODE,DESCRIPTION,DEPARTMENT,ATTRIBUTE1DESC,ATTRIBUTE2DESC,ATTRIBUTE3DESC,ATTRIBUTE4DESC,ATTRIBUTE5DESC
2,Test Stock Item Two,402,1.25L Bottle,Bourbons,Cab Merlot,Australia,Contains Egg
The Attribute Descriptions included in the above Import file example are shown below in the Stock Item Attributes window (accessed via Back Office > File > Sales > Stock Item Attributes):
After the item has been imported using the sample import file shown above, the Attributes will be assigned to the item as shown in the example below (bottom-right corner in the Attributes section).
If you need to import Alternate Codes, the stock items must already exist in the database.
Create a file which repeats the stock item code on a new line, with the unique scan code beside each of the stock items it is an alternate for.
Note that when importing Alternate Codes for a Stock Item that does not have a Scan Code, the first Alternate Code for the Stock Item in the import file will be used to populate the Scan Code for the Stock Item, then any subsequent Alternate Codes for the Stock Item will be added to the item.
CODE, SCANCODE
CODE,SCANCODE
331087,163429087162340875
331087,347098134098134098
331012,765013726407813640
You also have the option to repeat the SCANCODE heading the number of times required for each Scan Code.
This can be useful in scenarios where you need to add numerous Alternate Codes for either a single Stock Item or multiple Stock Items.
Example below for a single Stock Item with numerous Alternate Codes:
CODE,SCANCODE,SCANCODE
331087,163429087162340875,347098134098134098
Example below for numerous Stock Items with numerous Alternate Codes:
CODE,SCANCODE,SCANCODE
331087,163429087162340875,347098134098134098
331088,126851598456204456,526845612684455984
331089,365458944002658944,478264125893345891
All stock item codes, indirect and component, must already exist in the database.
When filling in details for the QUANTITY field they must be expressed as a fraction or a decimal.
Fractions work easier but it is purely preference.
INDIRECTCODE, COMPONENTCODE, QUANTITY
Minimum Requirement for Import: INDIRECTCODE, COMPONENTCODE, QUANTITY
Stock item codes must already exist in the database.
It is possible to have this field in the Stock Items file.
You will need to re-import the file after running the initial import, effectively modifying all Stock Items.
CODE, STKLEVEL, REORDLEVEL, RECOMLEVEL, CARTONQTY.
Idealpos allows you to import stock levels for stock item fields for specific locations when you have multiple locations in the database.
Set up different Headers called STKLEVELxxx, CARTONQTYxxx, RECOMLEVELxxx and REORDLEVELxxx where xxx is the location you want to import the details to.
You can still import the fields using the STKLEVEL, CARTONQTY, RECOMLEVEL and REORDLEVEL headers.
These fields will import the details to the location of the local terminal performing the import only.
Minimum Requirement for Import: CODE, STKLEVEL, REORDLEVEL, RECOMLEVEL
Customers have the ability of being automatically imported at a set daily time or at regular intervals throughout the day.
If wanting to use this feature, you must save the import file as cust.txt and save the file into the Idealpos folder default location - C:\ProgramData\Idealpos Solutions\Idealpos. Import Daily Time must be entered in 24hr format E.g. 14:30.
Go to Setup > Global Options > Customers.
Open Balance field will only import on new customers, not existing.
UDTEXT and UDYESNO are fields that you can set and name under Global Options.
When importing these files they must remain as UDTEXT and UDYESNO.
CODE, LASTNAME, FIRSTNAME, MIDDLENAME, TITLE, ADDRESS1, ADDRESS2, SUBURB, STATE, POSTCODE, PHONE, FAX, MOBILE, EMAIL, GENDER, COMPANY, OCCUPATION, MARITALSTATUS, DOB, DOB2, NEXTOFKIN, CONTACTNO, COMMENTS1, COMMENTS2, TYPE, GROUP, PASSWORD, MAILOUT, AUTODISCOUNT, ACCOUNT, CREDITLIMIT, PRICELEVEL, ABN, SCAN, OTHERCODES, OPENBAL, UDTEXT1, UDTEXT2, UDTEXT3, UDTEXT4, UDTEXT5, UDTEXT6, UDTEXT7, UDTEXT8, UDTEXT9, UDTEXT10, UDYESNO1, UDYESNO2, UDYESNO3, UDYESNO4, UDYESNO5, DISCONTINUED, POINTS, POINTSADJ, BARTAB, MASTERACCOUNT, CHANGED, PHOTO, SALESPROMPT, DELIVERYADDRESS1, DELIVERYADDRESS2, DELIVERYSUBURB, DELIVERYSTATE, DELIVERYPOSTCODE, ROOMINTERFACE.
Minimum Requirement for Import:
Field: | Length: | Notes: |
CODE | 10 | |
LASTNAME | 30 | |
FIRSTNAME | 15 | |
MIDDLENAME | 15 | |
TITLE | 4 | |
ADDRESS1 | 35 | |
ADDRESS2 | 35 | |
SUBURB | 35 | |
STATE | 3 | |
POSTCODE | 10 | |
PHONE | 20 | |
FAX | 20 | |
MOBILE | 20 | |
255 | ||
GENDER | 1 | Enter M or F |
COMPANY | 40 | |
OCCUPATION | 30 | |
MARITALSTATUS | 10 | |
DOB | 10 | |
DOB2 | 10 | |
NEXTOFKIN | 30 | |
CONTACTNO | 15 | |
COMMENTS1 | 50 | |
COMMENTS2 | 50 | |
TYPE | 30 | |
GROUP | ||
PASSWORD | 10 | |
MAILOUT | 1 | 1=Enabled/checked, 0=Disabled/unchecked |
AUTODISCOUNT | 4 | |
ACCOUNT | 1 | 1=Enabled/checked, 0=Disabled/unchecked |
CREDITLIMIT | 10 | Character limit includes decimal place |
PRICELEVEL | 2 | Value must be between 1 and 12. |
ABN | 14 | |
SCAN | 13 | |
OTHERCODES | 20 | |
OPENBAL | This field is used for importing any outstanding balances (dollar value) that the Customer owes. Enter the amount as a decimal value (e.g. enter 10.00 to import an outstanding balance of $10.00) | |
UDTEXT1 | 30 | |
UDTEXT2 | 30 | |
UDTEXT3 | 30 | |
UDTEXT4 | 30 | |
UDTEXT5 | 30 | |
UDTEXT6 | 30 | |
UDTEXT7 | 30 | |
UDTEXT8 | 30 | |
UDTEXT9 | 30 | |
UDTEXT10 | 30 | |
UDYESNO1 | 1 | 1=Enabled/checked, 0=Disabled/unchecked |
UDYESNO2 | 1 | 1=Enabled/checked, 0=Disabled/unchecked |
UDYESNO3 | 1 | 1=Enabled/checked, 0=Disabled/unchecked |
UDYESNO4 | 1 | 1=Enabled/checked, 0=Disabled/unchecked |
UDYESNO5 | 1 | 1=Enabled/checked, 0=Disabled/unchecked |
DISCONTINUED | 1 | 1=Discontinued, 0=Not Discontinued |
POINTS | The number of points to set for the customer | |
POINTSADJ | Points adjustment. | |
BARTAB | 1 | 1=Enabled/checked, 0=Disabled/unchecked |
MASTERACCOUNT | 10 | This is the Code of the Master Account to link to |
CHANGED | 1 | 1=Changed, 0=Not Changed |
PHOTO | 60 | Image folder path and image name |
SALESPROMPT | 30 | |
DELIVERYADDRESS1 | 35 | |
DELIVERYADDRESS2 | 35 | |
DELIVERYSUBURB | 35 | |
DELIVERYSTATE | 3 | |
DELIVERYPOSTCODE | 10 | |
ROOMINTERFACE | 1 | 1=Enabled for Room Interface 0=Disabled for Room Interface |
Only the fields for CODE and NAME are required to import suppliers.
CODE, NAME, ADDRESS, ADDRESS2, SUBURB, STATE, POSTCODE, PHONE, FAX, MOBILE, EMAIL, CONTACT, CREDITLIMIT, ABN, ACCOUNTNO, ORDERFORMAT, DELIVERYMETHOD, ACCOUNTING
Minimum Requirement for Import: CODE, NAME
To import Supplier Stock Codes for existing Stock Items, go to:
Back Office > Utilities > Import Data > Stock Items
Import Format Header Details:
CODE = The Stock Item Code
SUPP=The Supplier Code
SUPPCODE = The Supplier Order Code
Import Format:
CODE,SUPP,SUPPCODE
308104,19,1007C330
308105,19,1007K50
Starting from Idealpos 7 Build 26 and onwards, the import of Gift Vouchers across terminals is now supported by going to:
Back Office > Utilities > Import Data > Gift Vouchers
Here are some sample CSV file formats for importing.
Note that the file itself should not include any headers:
All fields (Code, Name, Type [1-10], Balance, Expiry Date, Site [0 for Global Redemption]):
Sample file contains:
60312,Pat Smith,1,44.86,2016-03-11,0
60313,Tim Rogers,2,55.96,2016-12-05,0
60314,Paul Short,1,76.00,2016-03-11,0
60315,Chris Clows,2,86.25,2016-12-05,0
60316,Suzy Madison,1,11.23,2016-03-11,0
60317,Amanda Jones,2,8.07,2016-04-05,0
See the below example showing the data once it has been imported:
All fields must be included in the CSV file.
Fields that are mandatory to contain data are (Code, Balance).
Sample File contains:
50336,,,22.00,,
50337,,,44.00,,
50338,,,66.00,,
50339,,,88.00,,
Example below of data once imported:
Importing Gift Vouchers for existing Customers
If you want to associate the Gift Voucher with an existing Customer, you can use the Customer's Code in the Code field.
Note that you will also need to go to: Global Options > Credit Notes / Gift Vouchers > Gift Vouchers, and tick the Gift Vouchers use Customer Code option.
Here is an example file for Customer Code = 6, where only the Code, Balance, Expiry and Site fields are being imported:
6,,,54.86,2017-06-11,0
Then when you bring that Customer into POS and select the Gift Voucher tender, it will automatically use that Customer's Gift Voucher:
Note!
If you specify a Site on the Gift Voucher, in the database the Site will be set and the "allowGlobalRedemption" flag will be false.
This means that while the "Gift Vouchers can be redeemed at any Site" option in Setup > Global Options > Credit Notes/Gift Vouchers is set on, the imported Gift Voucher will not be usable.
If you do NOT specify a Site, e.g.:
The Gift Voucher will be set as Site 0 and "allowGlobalRedemption" as true.
In this case, the imported Gift Voucher can be redeemed ONLY when the "Gift Vouchers can be redeemed at any Site" option is ENABLED.
From Idealpos v8 Build 12 and newer, Modifier Grids can be specified during the import of a Stock Item.
The Modifier Grid/POS Screen Grid must already exist in the database; this is simply used for populating a Stock Item's Modifier Grids field which is available via the Advanced Tab of the Stock Item > Modifier Grids.
Modifier Grids must be listed as a pipe separated list within a MODIFIERGRIDS field header.
E.g. To specify a Modifier Grid of 1 and 10 for a Stock Item, this must be entered as "1|10" in the MODIFIERGRIDS column of the Stock Item's record in the Import file.